# -*- coding: utf-8 -*-
"""
Created on Tue Aug  6 14:27:09 2024


@author: jzz
"""


import os
import pandas as pd
import matplotlib.pyplot as plt
import scipy.stats as stats
import numpy as np
import database_new_student as database_new
import copy
from dateutil.relativedelta import relativedelta
import datetime
import statsmodels.api as sm
import warnings
from scipy.stats import ttest_ind
from scipy.stats import f_oneway
warnings.filterwarnings("ignore")
plt.style.use('seaborn-v0_8')  # plt.style.use('dark_background') 
plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号
plt.rcParams['font.sans-serif']=['SimHei'] #用来正常显示中文标签



def find_and_extract_columns(root_dir, target_filename, column_name):
    # 用来存储读取的列数据
    dataframes = []

    # 遍历根目录下的所有子文件夹和文件
    for subdir, _, files in os.walk(root_dir):
        for file in files:
            if file == target_filename:
                file_path = os.path.join(subdir, file)
                folder_name = os.path.basename(subdir)  # 获取文件夹的名称
                # 读取Excel文件
                
                df = pd.read_excel(file_path, index_col=0) 
                # 提取指定的列并重命名
                if column_name in df.columns:
                    extracted_column = df[[column_name]].copy()
                    extracted_column.columns = [folder_name]  # 将列名改为文件夹名称
                    dataframes.append(extracted_column)
                    

    # 将所有提取的列拼接在一起
    if dataframes:
        combined_df = pd.concat(dataframes, axis=1)
        return combined_df
    else:
        return None
#%% zhuchengxu

def main(number):
    k=5#行业数量
    # 设定根目录
    root_directory = 'C:/Users/jzz/Desktop/全行业-无未来数据多行测试'
    result_df1 = find_and_extract_columns(root_directory, '最优参数回测过程'+str(number)+'.xlsx', '是否实际持有')
    
    
    target = pd.read_excel('C:/Users/jzz/Desktop/申万一级行业指数行情''.xlsx',index_col=0)
    target = pd.DataFrame(target)
    target = target.drop(columns=['银行','非银金融','环保'])
    start_date1 = '2015-12-31'
    end_date1 = '2024-7-31'
    target = target.resample('m').last()
    target = target.truncate(start_date1,end_date1)
    target = target.pct_change()
    target = target.fillna(0)
    target = target.iloc[0:]
    target_flag = pd.DataFrame()
    target_flag['全行业平均收益率'] = target.mean(axis=1)
    target_flag['全行业平均收益率'].iloc[0]=0
    
    
    #输入万得全A
    yunwind = database_new.YunWind()
    sql2 = "Select TRADE_DT,S_DQ_CLOSE from AINDEXWINDINDUSTRIESEOD where S_INFO_WINDCODE='881001.WI'"
    target2 = yunwind.read_sql(sql2)
    target2['TRADE_DT'] = pd.to_datetime(target2['TRADE_DT'])  # 转换为日期类型
    target2.set_index('TRADE_DT', inplace=True)
    
    
    result_df1=result_df1.fillna(0)
    result_df1.to_excel(root_directory+'/'+str(k)+'各行业仓位'+str(number)+'.xlsx')
    # 创建一个新的 DataFrame 来存储结果
    result_df = pd.DataFrame(0, index=result_df1.index, columns=result_df1.columns)
    
    # 对每一行找到最大的k个数，并设置为1
    for i in range(result_df1.shape[0]):
        # 找到前k个最大的值的列索引
        top5_indices = result_df1.iloc[i].nlargest(k,keep='first').index
        
        # 将这些位置设置为1
        result_df.loc[result_df1.index[i], top5_indices] = 1
    result_df.iloc[0]=0
    result_df.to_excel(root_directory+'/仓位'+str(number)+'.xlsx')
    
    result_df = result_df[target.columns]
    target[result_df == 0] = 0
    target['平均收益率']=target.sum(axis=1)/k
    target['策略净值']= (1+target['平均收益率']).cumprod()  #累乘
    table = pd.concat([target,target2.resample('m').last()],
                            axis=1).sort_index().dropna()
    
    table = pd.concat([table,target_flag],
                            axis=1).sort_index().dropna()
    table['万得全A净值']=table['S_DQ_CLOSE']/table.loc[table.index[0],'S_DQ_CLOSE']
    table['全行业平均净值']=(1+table['全行业平均收益率']).cumprod()
    table=table.rename(columns={'S_DQ_CLOSE':'万得全A'})
    table['策略相对万得全A超额净值']=table['策略净值']/table['万得全A净值']
    table['策略相对全行业平均净值超额净值']=table['策略净值']/table['全行业平均净值']
    len_nav = table.shape[0]
    annual_yieldindustry=(table['全行业平均净值'].iloc[-1]/table['全行业平均净值'].iloc[0])**(12/len_nav)-1
    annual_yieldindustry1=(table['全行业平均净值'].iloc[-32]/table['全行业平均净值'].iloc[0])**(12/(len_nav-31))-1
    annual_yieldindustry2=(table['全行业平均净值'].iloc[-1]/table['全行业平均净值'].iloc[-31])**(12/31)-1


    annual_yield = (table['策略净值'].iloc[-1]/table['策略净值'].iloc[0])**(12/len_nav)-1
    annual_yield1 = (table['策略净值'].iloc[-32]/table['策略净值'].iloc[0])**(12/(len_nav-31))-1
    annual_yield2 = (table['策略净值'].iloc[-1]/table['策略净值'].iloc[-31])**(12/31)-1

    annual_yield_industry=annual_yield-annual_yieldindustry
    annual_yield_industry1=annual_yield1-annual_yieldindustry1
    annual_yield_industry2=annual_yield2-annual_yieldindustry2

    print('相对行业平均超额收益率年化：（总体）',annual_yield_industry)
    print('相对行业平均超额收益率年化：（样本内）',annual_yield_industry1)
    print('相对行业平均超额收益率年化：（样本外）',annual_yield_industry2)
    
    table.to_excel(root_directory+'/最优'+str(k)+'个轮动收益情况'+str(number)+'.xlsx')

 
    fig, ax = plt.subplots()
    ax.plot(table['策略净值'], label='策略净值')
    ax.plot(table['策略相对万得全A超额净值'], label='策略相对万得全A超额净值')
    ax.plot(table['策略相对全行业平均净值超额净值'], label='策略相对全行业平均超额净值')
    ax.set_title('行业轮动'+str(number))
    ax.set_xlabel('日期')
    ax.set_ylabel('净值')
    ax.legend()

for number in range(20,21):
    main(number)
